{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4106c047-972d-4096-9099-916bbb642985",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from scipy.optimize import minimize"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c896f423-48e0-493e-b4d1-9ecc769a5f28",
   "metadata": {},
   "source": [
    "## 开始优化\n",
    "现在有数据：<br />\n",
    "1. 沪深300指数数据：df300\n",
    "2. 沪深300指数近3年每天的数据：df300_daily\n",
    "3. 成分股近3年每天的数据：df\n",
    "4. 成分股近3年涨跌幅时序数据：df1\n",
    "5. 指数权重前20支股票的代码与权重值：df20\n",
    "<br />\n",
    "\n",
    "目标是最小化跟踪误差，找到一组不超过20支股票买入的权重w"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "295c1f0a-ab2a-4f13-9b19-0cda2a3b0185",
   "metadata": {},
   "source": [
    "## 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4c1512ef-a215-4214-be4d-592d873aa765",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "H:\\anaconda3\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "# 指数权重前20的成分股近3年每天的数据\n",
    "df=pd.read_excel(\"df_all_top_20.xlsx\",index_col=0)\n",
    "# 指数权重前20的成分股近3年收盘时序数据\n",
    "# df1=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"pct_chg\"])\n",
    "df2=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"close\",\"pre_close\"])\n",
    "# 沪深300指数基本数据\n",
    "df300=pd.read_excel(\"df_300_w.xlsx\",index_col=0)\n",
    "df20=df300.loc[:19,:] # 指数权重前20的成分股基本数据\n",
    "# 沪深300指数近3年每天的数据\n",
    "df300_daily=pd.read_excel('沪深300指数近3年.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b9f3d96-0fc5-4928-ad6e-2922212d7fce",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_code_20=df20['ts_code'].tolist()\n",
    "close300=df300_daily[['日期Date','收盘Close']]\n",
    "index2codes=ts_code_20\n",
    "closei=df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "330d9372-fc04-46af-914f-f3c1d60e3041",
   "metadata": {},
   "source": [
    "## 定义目标函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d2cd3ab5-267d-4f4f-a889-da8290e0abb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def objective2(x):\n",
    "    # 最小化跟踪误差\n",
    "    days=len(close300)\n",
    "    track_error=0\n",
    "    earning_rate=[]\n",
    "    for i in range(days):\n",
    "        if i==0:\n",
    "            continue\n",
    "        # 计算20支股票组合的当日收益率：(当日收盘价 - 前一日收盘价) / 前一日收盘价\n",
    "        earning_rate_i=0\n",
    "        date=close300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=closei.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后计算当日收益率\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                earning_rate_i_j=np.sum((item['close']-item['pre_close'])/item['pre_close'])\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                earning_rate_i_j=0\n",
    "            earning_rate_i+=earning_rate_i_j*x[j]\n",
    "        # 找到当日沪深300的收益率\n",
    "        earning_rate300_i=(close300.loc[i,'收盘Close']-close300.loc[i-1,'收盘Close'])/close300.loc[i-1,'收盘Close']\n",
    "        # 当日跟踪误差误差(单位：1)\n",
    "        earning_rate.append(earning_rate_i-earning_rate300_i)\n",
    "    # 求标准差\n",
    "    track_error=np.std(earning_rate)\n",
    "    return track_error"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebe9d39a-fa80-4783-8398-7eea4d531008",
   "metadata": {},
   "source": [
    "## 定义约束条件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3e4bb336-47a6-4235-af69-4eb65c16bca7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义约束条件\n",
    "def constraint1(x):\n",
    "    return np.sum(x)-1        # 等式约束  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c83a9171-f9ae-4a57-a5d9-78584580126c",
   "metadata": {},
   "source": [
    "## 给出变量取值范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "68d2283a-f0a3-4a0b-945c-c4efb019de1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "nums=20\n",
    "bounds=[]\n",
    "for _ in range(nums):\n",
    "    bounds.append((0.0,1))\n",
    "bounds=tuple(bounds)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05191e55-5ef6-4e95-b45d-fa9674474b47",
   "metadata": {},
   "source": [
    "## 开始优化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b60bdc31-5e26-4849-bf52-65e64a8cc043",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "目标值: 0.00372817479389723\n",
      "最优解为\n",
      "[0.10987006 0.11791127 0.07166989 0.05116914 0.05638201 0.02532538\n",
      " 0.03372307 0.0392564  0.02532538 0.04249278 0.08516374 0.02532538\n",
      " 0.07596878 0.00367967 0.02080693 0.02532538 0.02532538 0.0840994\n",
      " 0.05585457 0.02532538]\n"
     ]
    }
   ],
   "source": [
    "# 开始\n",
    "con1 = {'type': 'eq', 'fun': constraint1}\n",
    "cons = ([con1])  \n",
    "\n",
    "x0=np.array([0.05]*20) #定义初始值\n",
    "solution = minimize(objective2, x0, method='SLSQP', \\\n",
    "                bounds=bounds, constraints=cons)\n",
    "\n",
    "x = solution.x\n",
    "\n",
    "print('目标值: ' + str(objective2(x)))\n",
    "print('最优解为')\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d60eef99-f8fc-4ca1-94e6-65d48e527e0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " message: Optimization terminated successfully\n",
       " success: True\n",
       "  status: 0\n",
       "     fun: 0.00372817479389723\n",
       "       x: [ 1.099e-01  1.179e-01 ...  5.585e-02  2.533e-02]\n",
       "     nit: 12\n",
       "     jac: [-1.668e-04  1.136e-05 ...  3.009e-04  0.000e+00]\n",
       "    nfev: 252\n",
       "    njev: 12"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "solution"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0003f578-f10e-4631-98bd-27b099dfc4a3",
   "metadata": {},
   "source": [
    "## 评价指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b86f23dc-897a-43d8-8288-8628fd488c8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "eva_index={}\n",
    "# 该组合跟踪误差\n",
    "track_error=objective2(x)\n",
    "eva_index['跟踪误差']=track_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "43b4b22b-7de7-405f-aff9-d7c885393abd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_total_return(x):\n",
    "    # 计算收益率\n",
    "    days=len(close300)\n",
    "    total_return=0\n",
    "    daily_returns=[]\n",
    "    for i in range(days):\n",
    "        if i==0:\n",
    "            continue\n",
    "        # 计算20支股票组合的当日收益率：(当日收盘价 - 前一日收盘价) / 前一日收盘价\n",
    "        earning_rate_i=0\n",
    "        date=close300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=closei.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后计算当日收益率\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                earning_rate_i_j=np.sum((item['close']-item['pre_close'])/item['pre_close'])\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                earning_rate_i_j=0\n",
    "            earning_rate_i+=earning_rate_i_j*x[j]\n",
    "        total_return+=earning_rate_i\n",
    "        daily_returns.append(earning_rate_i)\n",
    "    daily_returns=np.array(daily_returns)\n",
    "\n",
    "    return total_return,daily_returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "df57bc66-be60-4b3b-ae88-621734c50204",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 年化收益率\n",
    "total_return,daily_returns=get_total_return(x)\n",
    "n=3 # 取的是近3年的数据\n",
    "annualized_return = (1 + total_return) ** (1 / n) - 1\n",
    "eva_index['年化收益率']=annualized_return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ae8073b4-6fe8-48e9-867b-5371181e30a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 年化波动率\n",
    "# 计算日波动率（日收益率的标准差）\n",
    "daily_volatility=np.std(daily_returns, ddof=1)\n",
    "# 将日波动率年化\n",
    "annualized_volatility=daily_volatility * np.sqrt(252)\n",
    "eva_index['年化波动率']=annualized_volatility"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1f23f294-3502-4abb-9b06-280645caeb49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 夏普比率=（策略年化收益率 - 无风险年化收益率 ） / 策略年化波动率\n",
    "R_p = annualized_return  # 投资组合年化回报率\n",
    "R_f = 0.0125  # 无风险利率：3年期国债收益率\n",
    "sigma_p = annualized_volatility  # 投资组合的年化波动率\n",
    "sharpe_ratio = (R_p - R_f) / sigma_p\n",
    "eva_index['夏普比率']=sharpe_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "0b9b8f2c-9793-434d-884b-bf29bcac7af4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'跟踪误差': 0.00372817479389723,\n",
       " '年化收益率': 0.17805870265320717,\n",
       " '夏普比率': 0.25095152684585087,\n",
       " '年化波动率': 0.17805870265320717}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eva_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bb68cd1-cc29-4936-aaa4-808350779c03",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48be816c-12af-4e95-96c2-494cc3ff4c87",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2479a63-998a-404b-bdda-c18124be3dcc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
